﻿Write-Host "spAdm-Kerberos.Helpers loaded" -ForegroundColor DarkYellow

Function update-spAdm-File ($file, $Value) {

$file = Get-Content $file	
$file = $file | Out-String

    If ($file.contains($Value)) {
		        
        return $true

    } else {

        return $false
			    
	}
}
   
 
Function New-spAdm-folder ($path)
{
    If (!(Test-Path -Path $path -PathType Container))
    {

        Write-Host -ForegroundColor White " - $path doesn't exist; creating..."
        Try 
        {
            New-Item -Path $path -ItemType Directory | Out-Null
        }
            
        Catch
        {               
            Write-Warning " - $($_.Exception.Message)"
            Throw " - Could not create folder $path!"
        }
    }
}


Function write-spAdm ($Result, $Message, $Silent, $ErrorMessage) {

Write-Debug " FUNCTION write-spAdm"

	if ($Result -eq $true)  {
                       
        If (!$Silent) {
                    
            if ($Message) {Write-Host -ForegroundColor White "$Message.." -NoNewline}
		    Write-Host -ForegroundColor Green "..[OK]"

        }

	} elseif ($Result -eq $False){
        		
        If ($Errormessage) {Write-Host -ForegroundColor Red "Error: $ErrorMessage"} Else {Write-Host -ForegroundColor Red "........[Oops]"}
        
	} else {Write-Host -ForegroundColor White -BackgroundColor Red "Error: We should not be here... troubleshoot"}


Write-Debug " FUNCTION write-spAdmCom-Result.FINSIHED"

}

function build-spAdm-Spn-Base () {

Write-Debug " FUNCTION build-spAdm-Spn-Base"

    Write-Host "  - Adding core functions" -NoNewline

    IF (Test-Path $SetspAdmKerberos) {

        Remove-Item $SetspAdmKerberos

         
    }
        
   
    $Url = $null
    $Account = $null

    Write-Output "function Set-spAdm-Spn (`$spn,`$Account) { " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output ""  | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    Write-Host `" - Adding Spn `$spn to `$account`" -NoNewline " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    `$AddSPN = setspn -s `$spn `$Account" | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    if (`$?) {Write-Host `"..[ok]`" -ForegroundColor Green}  else {Write-Host `" Error`" -ForegroundColor Red} " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output ""  | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "}" | Out-File $SetspAdmKerberos -Width 400 -Append


    Write-Output "function Set-spAdm-TrustforDelegation (`$Account, `$DelegateSPN) { " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output ""  | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    if (([string]::IsNullOrEmpty(`$DelegateSPN))) {`$decValue = `"524288`"} else {`$decValue = `"16777216`"} " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output ""  | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    `$AccountName = `$Account.split(`"\`")[1]; if (([string]::IsNullOrEmpty(`$AccountName))) {`$AccountName = `$Account} " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    write-host `"Setting Delegation on `$ACCOUNT to `$decValue`" -NoNewline " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output ""  | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    `$adsiSearcher = New-Object DirectoryServices.DirectorySearcher [ADSI]`$null " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    `$adsiSearcher.filter = `"(&(objectClass=user)(sAMAccountName=`$accountName))`" " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    `$adsiSearcherResult = `$adsiSearcher.FindOne() " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output ""  | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    If (([string]::IsNullOrEmpty(`$adsiSearcherResult))) {Write-Host `" - User Not found`"; return} " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output ""  | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    `$user = `$adsiSearcherResult.GetDirectoryEntry() " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    `$set = (`$user.userAccountControl[0] -band `$decValue) -ne 0 " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    If (!([string]::IsNullOrEmpty(`$DelegateSPN))) {Write-Host `" - Checking Delegation to `$DelegateSPN`" -NoNewline; `$Match = `$false; `$user.Properties[`"msDS-AllowedToDelegateTo`"] | % {if(`$_ -eq `$DelegateSPN){`$Match = `"Match`"; Write-Host `" - Found`"}} " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output ""  | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "        if (!`$set) {WRITE-HOST `" - Setting `$decValue`"; `$uac=`$user.userAccountControl[0] -bor `$decValue; `$User.userAccountControl[0]=`$uac; `$User.CommitChanges();} else { WRITE-HOST `" - Already Set `$decValue`"} " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output ""  | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "        if (!`$Match) {write-Host `" - Adding`"; `$user.Properties[`"msDS-AllowedToDelegateTo`"].Add(`$DelegateSPN) | Out-Null; `$user.CommitChanges()} " | Out-File $SetspAdmKerberos -Width 400 -Append     
    Write-Output "        Write-Host `"..[ok]`" " | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output ""  | Out-File $SetspAdmKerberos -Width 400 -Append
    Write-Output "    } ELSE {"| Out-File $SetspAdmKerberos -Width 400 -Append 
    Write-Output "        WRITE-HOST `" - Setting `$decValue`"; `$uac=`$user.userAccountControl[0] -bor `$decValue; `$User.userAccountControl[0]=`$uac; `$User.CommitChanges();" | Out-File $SetspAdmKerberos -Width 400 -Append 
    Write-Output "    } "| Out-File $SetspAdmKerberos -Width 400 -Append 
    Write-Output "} "| Out-File $SetspAdmKerberos -Width 400 -Append 

    write-spAdm -Result $true

    
Write-Debug " FUNCTION write-build-spAdm-Spn-Base.FINSIHED"

}

function build-spAdm-Spn-C2Wts () {

Write-Debug " FUNCTION build-spAdm-Spn-C2Wts"
    
    IF ((gwmi -Class win32_service | Select-Object name,startname,startmode,state,status | where { $_.Name -eq "c2wts" }).startname -eq 'LocalSystem'){
    Write-Host 'Skipping SPN setup for 'Claims to Windows Token Service'. Service running as 'local system' is unsupported.'
    }
    else {
        
    $c2wts = get-spserviceinstance | where {$_.TypeName -eq "Claims to Windows Token Service"}

    Write-Host "  - Constrained Delegation in place? " -NoNewline
    Write-Host $c2wts.Service.ProcessIdentity.Username -ForegroundColor Magenta -NoNewline 
    $EnabledAccountForDelegationsConstrained = Check-spAdm-AccountForDelegation -type user -accountName $c2wts.Service.ProcessIdentity.Username -decValue 16777216
    if ($EnabledAccountForDelegationsConstrained){ 
    
        write-spAdm -Result $true

        # lets check that an SPN still exists

        $registeredSpn = setspn -l $c2wts.Service.ProcessIdentity.Username

        Write-Debug "SPN count $registeredSpn.count"
        if (!($registeredSpn.count -ge 2)) { # as in no SPN
                    
            Write-Host "   - No SPN, ignore that we have delegation" -NoNewline -ForegroundColor Red
            build-spAdm-Spn -account $c2wts.Service.ProcessIdentity.Username -Record "DummyC"
                
        }

    } else {
        
        Write-Host " - NO" -ForegroundColor Red
        Write-Output "# C2WTS" | Out-File $SetspAdmKerberos -Width 400 -Append
        
        build-spAdm-Spn -account $c2wts.Service.ProcessIdentity.Username -Record "DummyC" -type sp

    }
    }

  
Write-Debug " FUNCTION build-spAdm-Spn-C2Wts.FINSIHED"
}


function build-spAdm-Spn ($Account, $Record, $type) {
Write-Debug " FUNCTION build-spAdm-Spn-Dummy-And-delegation"

    $EnabledAccountForDelegations = $null

    # build SPN 

    if ($Record -like "Dummy*") { # any old SPN will do 

        # Note Adding a Arbitrary SPN to all service Applications is over kill, however will do no harm.
        # it is beeing added to enable that account to be constrained

        Write-Host "  - Generating SPN" -NoNewline

        #Ensure that we have unique SPN
        $spn = $Account.split("\")[1]; if (([string]::IsNullOrEmpty($spn))) {$spn = $Account}
        $Spn = "SP/spAdmArbitrarySpn" + $spn
        write-spAdm $?
        
    } elseif ($type -eq "http") {     #need to build spn from passed in record
        
        $spn = "http/" + $Record
    
    } elseif ($type -eq "SQL") {# NamedSQLInstance: 'MSSQLSvc/FQDN:[port | instancename]' OR DefaultSQLInstance: 'MSSQLSvc/FQDN:port | MSSQLSvc/FQDN'
        # http://msdn.microsoft.com/en-us/library/ms191153.aspx

        $spn = "MSSQLSvc/" + $Record.split(":")[0] + ":" + $Record.split(":")[1]
 
     } elseif ($type -eq "SQL_Browser") {# NamedSQLInstance: 'MSSQLSvc/FQDN:[port | instancename]' OR DefaultSQLInstance: 'MSSQLSvc/FQDN:port | MSSQLSvc/FQDN'
        # http://support.microsoft.com/kb/950599

        $spn = "MSOLAPDisco.3/" + $Record.split(":")[0] + ":" + $Record.split(":")[1]

    } Else {

        Write-Host "  - Invalid Type of SPN" -ForegroundColor Red; return 

    }

    #Check Account is valid
    if (($Account -like "<*") -or ($spn -like "*>")){

        $Createspn  = "# set-spAdm-spn -spn " +  $spn + " -Account " + $account

        Write-Host " - Adding Request $spn" -NoNewline

        
        if (update-spAdm-File -file $SetspAdmKerberos -Value $spn) {

            Write-Host " - Already Requested"
            
            
        } else {

            Write-Output $Createspn | Out-File $SetspAdmKerberos -Width 400 -Append
            write-spAdm $?

        }
    
    }
    Elseif ($Account -eq "NT AUTHORITY\LOCALSERVICE"){

        Write-Warning "Skipping adding '$type' SPN for unsupported local system account '$Account' on '$Record'."
    }
    Else {

    # check if spn is already registered to this account 
    
    Write-Host "  - Checking if SPN registered to $Account" -NoNewline
    $Registered = Check-spAdm-Spn-Exist -Account $account -spn $spn
    
    # ensure we have a unique spn
    If (!$Registered) {
            
        $Unique = $false
        $SpnQuery = $null

        Write-Host "  - Ensuring that SPN is unique" -NoNewline 

        if (($Record -like "*Dummy*")) {

            do {
            $adsiSearcher = New-Object DirectoryServices.DirectorySearcher [ADSI]$null;
            $adsiSearcher.filter = "(&(objectClass=user)(servicePrincipalName=$Spn))";
            $SpnQuery = $adsiSearcher.FindOne();
            if (!([string]::IsNullOrEmpty($SpnQuery))) {
            $spn = $spn + (Get-Random -minimum 100 -maximum 999);
            } else {
            $Unique = $true;
            }
            }
            
            
            while ($Unique -eq $false)
            write-spAdm $?


            

        } else { # test if SPN is registered to another account ...
           
            $adsiSearcher = New-Object DirectoryServices.DirectorySearcher [ADSI]$null;
            $adsiSearcher.filter = "(&(objectClass=user)(servicePrincipalName=$Spn))";
            $SpnQuery = $adsiSearcher.FindOne();

            if (!([string]::IsNullOrEmpty($SpnQuery))) {
                Write-Host " - $SPN already in use" -ForegroundColor Red; return # Error
                
            } else {

                write-spAdm -Result $?

            }
            
        }
        
        Write-Host "  - Adding SPN Request" -NoNewline
        $Createspn  = "set-spAdm-spn -spn " +  $spn + " -Account " + $account
        
        if (update-spAdm-File -file $SetspAdmKerberos -Value $spn) {

            Write-Host " - Already Requested"
            
            if ($spn -like "*http/*") {
            
                Write-Warning "SPN can't be registered to more than one service account "; sleep 10

            }
            
            
        } else {
            
            Write-Output $Createspn | Out-File $SetspAdmKerberos -Width 400 -Append
            write-spAdm $?

        }
        
                
    }




    if($type -ne "SQL"){

    Write-Host "  - Checking delegation level is ok" -NoNewline
    $EnabledAccountForDelegationsConstrained = Check-spAdm-AccountForDelegation -type user -accountName $account -decValue 16777216

    if (!$EnabledAccountForDelegationsConstrained) { 

        if ($Record -like "DummyC") { # need to enable for constrained delegation 

            Write-Host " - No" -ForegroundColor Red
            Write-Host "  - Adding constrained Delegation" -NoNewline
            $Delegation  = "Set-spAdm-TrustforDelegation -account " + $account + " -DelegateSPN " + $Spn

            if (update-spAdm-File -file $SetspAdmKerberos -Value $Delegation) {

                Write-host " - already exist"
            
            } else {
             
                Write-Output $Delegation | Out-File $SetspAdmKerberos -Width 400 -Append
                write-spAdm $?

            }

        } else { # good old basic will do as (DummyB or http or SQL)

            $EnabledAccountForDelegationsBasic = Check-spAdm-AccountForDelegation -type user -accountName $account -decValue 524288
            
            Write-Host " * Basic " -NoNewline
            
            if (!$EnabledAccountForDelegationsBasic) {

                Write-Host " - No" -ForegroundColor Red
                Write-Host "  - Adding Basic Delegation" -NoNewline
                $Delegation  = "Set-spAdm-TrustforDelegation -account " + $account
                if (update-spAdm-File -file $SetspAdmKerberos -Value $Delegation) {

                    Write-host " - already exist"
            
                } else {
                    Write-Output $Delegation | Out-File $SetspAdmKerberos -Width 400 -Append
                    write-spAdm $?

                }

            } else {

                write-spAdm -Result $true 

            }

        }
    
    
    } else {write-spAdm $?} # Account is constrained for delegation 
 
}
}
Write-Debug " FUNCTION build-spAdm-Spn-Dummy-And-delegation.FINSIHED"              
}

function build-spAdm-Spn-ServiceApplications () {

Write-Debug " FUNCTION build-spAdm-Spn-ServiceApplications"

    Write-Debug " - Creating set-spn request for Service Applications"

    $allServiceAppliations = Get-SPServiceApplication 

    Write-Output "# Services Applications" | Out-File $SetspAdmKerberos -Width 400 -Append

    foreach ($ServiceApplication in $allServiceAppliations) {

        # http://technet.microsoft.com/en-us/library/ee806870.aspx
        # http://support.microsoft.com/kb/2722087

        
        
        # Kerberos constrained delegation
        if (($ServiceApplication.TypeName -eq "Excel Services Application Web Service Application") -or       
            ($ServiceApplication.TypeName -eq "PerformancePoint Service Application") -or                           
            ($ServiceApplication.TypeName -eq "Visio Graphics Service Application")) {

            Write-Host "  -"$ServiceApplication.DisplayName -ForegroundColor Green -NoNewline
            
            Write-Host "   - Constrained Delegation in place? " -NoNewline
            Write-Host  $ServiceApplication.applicationpool.processaccountname -ForegroundColor Magenta -NoNewline
            $EnabledAccountForDelegationsConstrained = Check-spAdm-AccountForDelegation -type user -accountName $ServiceApplication.applicationpool.processaccountname -decValue 16777216
            if ($EnabledAccountForDelegationsConstrained){ 
                
                write-spAdm -Result $true

                # lets check that an SPN still exists

                $registeredSpn = setspn -l $ServiceApplication.applicationpool.processaccountname

                Write-Debug "SPN count $registeredSpn.count"
                if (!($registeredSpn.count -ge 2)) { # as in no SPN
                    
                    Write-Host "   - No SPN, ignore that we have delegation" -NoNewline -ForegroundColor Red
                    build-spAdm-Spn -account $ServiceApplication.applicationpool.processaccountname -Record "DummyC"
                
                }

            } else {
            
                Write-Host "  - No" -ForegroundColor Red
                build-spAdm-Spn -account $ServiceApplication.applicationpool.processaccountname -Record "DummyC"

            }
            
        }                      
            
        # Kerberos basic or constrained delegation    
        elseif (($ServiceApplication.TypeName -eq "Business Data Connectivity Service Application") -or           
                ($ServiceApplication.TypeName -eq "Access Services") -or                                     
                ($ServiceApplication.TypeName -eq "Project Application Services") -or                                      
                ($ServiceApplication.TypeName -eq "SQL Server Reporting")) {

                Write-Host "  -"$ServiceApplication.DisplayName -ForegroundColor Green -NoNewline
                
                # Notify additional steps required if SQL Server Reporting is using Kerberos
                If ($ServiceApplication.TypeName -eq "SQL Server Reporting"){
                Write-Warning "  - Script does not cover Kerberos setup for the Reporting Services SQL Instance service account. Script does support Kerberos setup of SharePoint 'SQL Server Reporting' Service Application."
                }

                Write-Host "  - Constrained or Basic delegation in place? " -NoNewline
                Write-Host $ServiceApplication.applicationpool.processaccountname -ForegroundColor Magenta -NoNewline

                $EnabledAccountForDelegationsBasic = Check-spAdm-AccountForDelegation -type user -accountName $ServiceApplication.applicationpool.processaccountname -decValue 524288
                
                if ($EnabledAccountForDelegationsBasic){ 
    
                    write-spAdm -Result $true

                    # lets check that an SPN still exists

                    $registeredSpn = setspn -l $ServiceApplication.applicationpool.processaccountname

                    Write-Debug "SPN count $registeredSpn.count"
                    if (!($registeredSpn.count -ge 2)) { # as in no SPN
                    
                        Write-Host "   No SPN, ignore that we have delegation" -NoNewline -ForegroundColor Red
                        build-spAdm-Spn -account $ServiceApplication.applicationpool.processaccountname -Record "DummyB"
                
                    }

                } else {

                    Write-Host "  - No" -ForegroundColor Red
                    build-spAdm-Spn -account $ServiceApplication.applicationpool.processaccountname -Record "DummyB" 

                }
           
        } else {

            Write-Debug "    - SPN not required $ServiceApplication.DisplayName" 
        }

    }

Write-Debug " FUNCTION build-spAdm-Spn-ServiceApplications.FINSIHED"

}


function build-spAdm-Spn-WebApplications () {

Write-Debug " FUNCTION build-spAdm-Spn-WebApplications"

    Write-Output "# Web Applications" | Out-File $SetspAdmKerberos -Width 400 -Append
    
    # Kerberos Guide says:
     
    # It may seem redundant to configure delegation from a service to itself, such as the portal service account delegating to the portal service application, 
    # but this is required in scenarios where you have multiple servers running the service. This is to address the scenario where one server may need to delegate 
    # to another server running the same service; for instance a WFE processing a request with a RSS viewer which uses the local web application as the data source. 
    # Depending on farm topology and configuration there is a possibility that the RSS request may be serviced by a different server which would require delegation 
    # to work correctly.’

    # This script will utlise basic... the above would indicate constrained
    # Constrained may also be required for infopath forms !!! 
    # Shall review @ a later date ... 
    
    $AllWebApps = Get-SPWebApplication
                
    foreach ($webApp in $AllWebApps)  { 

        Write-Host "  - Web Application - "$webApp.Name -ForegroundColor Green
           
        Write-Debug $webApp.ApplicationPool.UserName
            
        #$AllSpnsForAccount = setspn.exe -l $webApp.ApplicationPool.Username

        # Get all alternate access mappings
        $AllPossUrls = $null
        foreach ($URL in $webApp.AlternateUrls | Select-Object 'IncomingUrl') {$AllPossUrls = $AllPossUrls + $URL.IncomingUrl + ","}
        foreach ($SiteCollection in $webApp.sites | where {$_.HostHeaderIsSiteName -eq $true}) {$AllPossUrls = $AllPossUrls + $SiteCollection.url + ","}
        $AllPossUrls = ($AllPossUrls.TrimEnd(",")).split(",") | Get-Unique

        foreach ($Url in $AllPossUrls) {
            $URL = ([uri]$URL).DnsSafeHost
            $aRecord = Get-spAdm-DNS-HostARecord -Record $URL

            if ($aRecord -eq "Missing")  { # Error no dns record 
                write-spAdm -Result $false -ErrorMessage "No DNS record for  - $URL"
                # Script warns and continues
                Write-Warning " Script detected that DNS is missing. Setting SPNs where no A Record exists is not advisable." 
                $aRecord = $URL 

                       
            }

            Write-Host "  - A Record :"$aRecord
            build-spAdm-Spn -account $webApp.ApplicationPool.UserName -Record $aRecord -type http

        }

    Write-Debug " FUNCTION build-spAdm-Spn-WebApplications.FINSIHED"

    }

}
    
function build-spAdm-Spn-sql () {
Write-Debug " FUNCTION build-spAdm-Spn-sql"

$Query = "

--Query Instance Regestry Paths and store in temporary table, then lookup this Instance Path and store in parameter (required for Dynamic Port)
DECLARE @regPathValue VARCHAR(50)
DECLARE @instanceRegPath TABLE (InstanceName NVARCHAR(100), RegPath NVARCHAR(100))

	INSERT INTO @instanceRegPath (InstanceName, RegPath)
	EXEC   master..xp_instance_regenumvalues
       		@rootkey = N'HKEY_LOCAL_MACHINE',
       		@key     = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'
	SET @regPathValue = (select RegPath FROM @instanceRegPath WHERE InstanceName = @@SERVICENAME)


-- Query Instance details, store in temporary table and return all values
DECLARE @SQLService   VARCHAR(60), @AgentService VARCHAR(60), @BrowserService VARCHAR(60), @keyname VARCHAR(255), @Browserkeyname VARCHAR(255), @agentkeyname VARCHAR(255), @tcp_keyname VARCHAR(255),@tcp_port nvarchar(5), @TcpDynamicPorts nvarchar(5)
DECLARE @Report TABLE (ServerName NVARCHAR(255),SQLServiceAcct NVARCHAR(255), BrowserAcct NVARCHAR(255), AgentAcct NVARCHAR(255), PortNumber NVARCHAR(255), DynamicPorts NVARCHAR(255))
			
	SELECT 
    
	@keyname =   CASE WHEN CHARINDEX('\',@@SERVERNAME,1) <> 0
		THEN 'SYSTEM\ControlSet001\Services\MSSQL$' + @@SERVICENAME
		ELSE 'SYSTEM\ControlSet001\Services\MSSQLSERVER'
		END,

	@agentkeyname =  CASE WHEN CHARINDEX('\',@@SERVERNAME,1) <> 0
		THEN 'SYSTEM\ControlSet001\Services\SQLAGENT$' + @@SERVICENAME
		ELSE 'SYSTEM\ControlSet001\Services\SQLSERVERAGENT'
		END,

	-- For TCP Details lookup from Instance RegPath
	@tcp_keyname = 'SOFTWARE\Microsoft\Microsoft SQL Server\'+@regPathValue+'\MSSQLServer\SuperSocketNetLib\TCP\IPALL\',
                                
	@BrowserKeyName = 'SYSTEM\ControlSet001\Services\SQLBrowser'
            
	EXEC xp_regread @root_key   ='HKEY_LOCAL_MACHINE',     
		@key        = @keyname,
		@valuename  = 'ObjectName',
		@value      = @SQLService OUTPUT
                 
	EXEC xp_regread @root_key   = 'HKEY_LOCAL_MACHINE',
		@key        = @agentkeyname,
		@valuename  = 'ObjectName',
		@value      = @AgentService OUTPUT       
		
	EXEC xp_regread @root_key   = 'HKEY_LOCAL_MACHINE',
		@key        = @BrowserKeyName,
		@valuename  = 'ObjectName',
		@value      = @BrowserService OUTPUT       
		    
	EXEC xp_regread @root_key   = 'HKEY_LOCAL_MACHINE',
		@key        = @tcp_keyname,
		@valuename  = 'TcpPort', 
		@value      = @tcp_port OUTPUT   

	EXEC xp_regread @root_key   = 'HKEY_LOCAL_MACHINE',
		@key        = @tcp_keyname,
		@valuename  = 'TcpDynamicPorts', 
		@value      = @TcpDynamicPorts OUTPUT 
                                        
	INSERT INTO @Report (ServerName,SQLServiceAcct,AgentAcct,BrowserAcct,PortNumber,DynamicPorts)
	SELECT @@SERVERNAME, @SQLService,@AgentService,@BrowserService,@tcp_port,@TcpDynamicPorts

	SELECT * FROM @Report "

$Query2nd = "

            DECLARE @SQLService   VARCHAR(60), @keyname VARCHAR(255)
            DECLARE @Report TABLE (ServerName NVARCHAR(255),SQLServiceAcct NVARCHAR(255))
		
            SELECT
    
            @keyname = 'SYSTEM\CurrentControlSet\Services\MSSQLSERVER'

			EXEC master.dbo.xp_instance_regread @root_key   ='HKEY_LOCAL_MACHINE',     
                    @key        = @keyname,
                    @valuename  = 'ObjectName',
                    @value      = @SQLService OUTPUT

            INSERT INTO @Report (ServerName,SQLServiceAcct)
            SELECT @@SERVERNAME, @SQLService

            SELECT * FROM @Report "

    $DBServers = get-SPContentDatabase | Select-Object 'Server' | Get-Unique

    Write-Output "# Attached SQL servers" | Out-File $SetspAdmKerberos -Width 400 -Append
    
    foreach ($server in $DBServers.Server) {

        Write-Debug $server
        
        $SqlInstanceDetails = (Connect-spAdm-SqlDatabase -SQLServer $server -SqlQuery $Query) # Full query
        $SqlInstanceDetails2nd = (Connect-spAdm-SqlDatabase -SQLServer $server -SqlQuery $Query2nd) # Reduced query
        
        if($SqlInstanceDetails -ne $false){ # Successfully retrieved SQL Instance Details  
        
            $sqlInstanceSvcAccount = ($SqlInstanceDetails.Tables[0] | select-object 'SQLServiceAcct').SQLServiceAcct.split("\")[1]
            $sqlInstancePort = $SqlInstanceDetails.Tables.PortNumber
            $sqlInstanceName = ($SqlInstanceDetails.Tables[0] | select-object 'ServerName').ServerName.split("\")[1]
            $sqlInstanceDynamicPort = $SqlInstanceDetails.Tables.DynamicPorts
            $sqlInstanceBrowserSvcAccount = ($SqlInstanceDetails.Tables[0] | select-object 'BrowserAcct').BrowserAcct.split("\")[1]
        }

        If(($SqlInstanceDetails -eq $false) -and ($SqlInstanceDetails2nd -ne $false)){ # Failed to retrieve full SQl instance details, attempt to read from second query
            $sqlInstanceSvcAccount = ($SqlInstanceDetails2nd.Tables[0] | select-object 'SQLServiceAcct').SQLServiceAcct.split("\")[1]
            $sqlInstancePort = "<Port Number>"
            $sqlInstanceName = ($SqlInstanceDetails2nd.Tables[0] | select-object 'ServerName').ServerName.split("\")[1]
            $sqlInstanceDynamicPort = "<Dynamic Port Number>"
            $sqlInstanceBrowserSvcAccount = "<Browser Service Account>"
        }
        
        If(($SqlInstanceDetails -eq $false) -and ($SqlInstanceDetails2nd -eq $false)) { # Failed to retrieve cut down SQl instance details, set fill in values
        
            write-spAdm -Result $false -ErrorMessage " - Unable to run SQL queries on database $server"

            $sqlInstanceSvcAccount = "<Service Account>"
            $sqlInstancePort = "<Port Number>"
            $sqlInstanceName = "<Instance Name>"
            $sqlInstanceDynamicPort = "<Dynamic Port Number>"
            $sqlInstanceBrowserSvcAccount = "<Browser Service Account>"
        }

        $aRecord = Get-spAdm-DNS-HostARecord -Record $server

        if ($aRecord -eq "missing")  { # Error no dns record 
        
                 $aRecord = Check-spAdm-SQlAlias -Alias $server

        }
        
        if ($aRecord -ne $false) {

                 $aRecord = Get-spAdm-DNS-HostARecord -Record $aRecord
                 Write-Debug $aRecord
        }
        
            Else {
                     write-spAdm -Result $false -ErrorMessage " - Unable to Resolve aRecord for Database $server"
                     return 
            }
                                   
       
        

            # Warn if Dynamic Port detected on named instance - http://blogs.msdn.com/b/arvindsh/archive/2012/09/08/how-to-assign-a-static-port-to-a-sql-server-named-instance-and-avoid-a-common-pitfall.aspx
            if (($sqlInstanceDynamicPort -ne "<Dynamic Port Number>") -and (!([string]::IsNullOrEmpty($sqlInstanceDynamicPort)))) {

                    Write-Warning " - Dynamic Port Detected on named SQL instance $sqlInstanceName. Static ports are recommended"
                            
            }
            
            # Kerberos configuration for SQL named instance - http://msdn.microsoft.com/en-us/library/ms191153.aspx
            # Build named instance SPN, port not required in SPN - http://msdn.microsoft.com/en-us/library/ms191153.aspx
            # Kerberos configuration for default SQL instance
            # Skipping Dynamic Port check if default instance - http://support.microsoft.com/kb/823938
            # Skipping SPN for Browser Service as only required for named instance

            # Build SQL SPN for default instance (SPN based on port)
            

            $tempaRecord = $aRecord + ":" + $sqlInstanceName
            build-spAdm-Spn -account $sqlInstanceSvcAccount -Record $tempaRecord -type "SQL"
            build-spAdm-Spn -account $sqlInstanceBrowserSvcAccount -Record $tempaRecord -type "SQL_Browser"


            $tempaRecord = $aRecord + ":" + $sqlInstancePort
            build-spAdm-Spn -account $sqlInstanceSvcAccount -Record $tempaRecord -type "SQL"
            build-spAdm-Spn -account $sqlInstanceBrowserSvcAccount -Record $tempaRecord -type "SQL_Browser"
 
            
        
        }

Write-Debug " FUNCTION build-spAdm-Spn-sql.FINSIHED"
}


function Connect-spAdm-SqlDatabase ($SQLServer, $SqlQuery) {

    Write-Debug "Connect-spAdm-SqlDatabase"

    #Write-Host " - Running Query" -NoNewline

    Try {
    
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Server = $SQLServer; Integrated Security = True"

        
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = $SqlQuery
        $SqlCmd.Connection = $SqlConnection

        
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd
        $DataSet = New-Object System.Data.DataSet
        $Value = $SqlAdapter.Fill($DataSet)
        
        $SqlConnection.Close()

    } Catch {

        # write-spAdm -Result $False -ErrorMessage "Unable to Run SQL query";
        return $False

    }

    
    Write-Debug " Connect-spAdm-SqlDatabase.FINISHED"
    
    return $DataSet 

}


function Check-spAdm-SQlAlias ($Alias) {  
Write-Debug " Check-spAdm-SQlAlias"

    Write-Host " - Checking if $Alias is a SQL Alias" -NoNewline

    $regkey = "HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo"
    
    if (!(Test-Path $regkey)) {
           
        Write-Host " - No SQl alias on this server" -ForegroundColor Red
       

    } else {


        try {
            
            $exists = Get-ItemProperty $regkey $Alias -ErrorAction SilentlyContinue

                    
                if (($exists -eq $null) -or ($exists.Length -eq 0)) {
                    
                    Write-Host " - No" -ForegroundColor Red
                    return $false
                
                } else {

                   Write-Host " - Yes" -ForegroundColor Green
                   $DNSName = (($exists."$Alias").split(",")[1]).split("\")[0]
                   return $DNSName
                }
            
            } catch {

                return $false

            }
    }
Write-Debug " Check-spAdm-SQlAlias.FINISHED"

}




function Check-spAdm-Spn-Exist ($Account, $spn) {  
Write-Debug " FUNCTION Check-spAdm-Spn-Exist"

    Write-Debug " - $Account"
    Write-Debug " - $spn"

    if ((setspn -l $Account) -like "*$spn*") {
    
        Write-Host " - Yes" -ForegroundColor Green
        Return $true
        
    } else {
    
        Write-Host " - No" -ForegroundColor Red 
        return $false
        
    }
        

Write-Debug " FUNCTION Check-spAdm-Spn-Exist.FINSIHED"
}

function Check-spAdm-AccountForDelegation ([string]$type,[string]$accountName,[string]$decValue) {

    Write-Debug " - Type: $type"
    Write-Debug " - AccountName: $accountName"
    Write-Debug " - DecimalValue: $decValue"
  
    if ($accountName -like "*\*") {$accountName = $accountName.Split("\")[1]}
    
    Write-Debug " - AccountName: $accountName"

    $adsiSearcher = New-Object DirectoryServices.DirectorySearcher [ADSI]$null
    $adsiSearcher.filter = "(&(objectClass=$type)(sAMAccountName=$accountName))"
    $adsiSearcherResult = $adsiSearcher.FindOne()
    $user = $adsiSearcherResult.GetDirectoryEntry()
    Write-Debug $user.userAccountControl[0]
    return ($user.userAccountControl[0] -band $decValue) -ne 0

Write-Debug " Check-spAdm-AccountForDelegation.FINSIHED"
  
}

function Clean-spAdm-spns {

    foreach ($account in (Get-SPManagedAccount)) {
    
        $account  = ($account.username).tostring()
        Write-Host $account -NoNewline
        $Allspn = setspn -l $account
       

        if ($Allspn.count -ge 2) {write-host " - Start Deleting, total " ($Allspn.count -1)
    
            $finish = ($Allspn.count -1)
            $count = 1
            #Setspn -d  $account
            do {
                $spn = ((($Allspn[$count]) -replace '\s+', ' ').replace(' ',''))
                 Write-Host $count $spn; Setspn -d $spn $account | Out-Null; Write-Host "..[ok]" -ForegroundColor Green
                 if ($Allspn.count -ge 3) {$count = $count + 1} else {$count = $finish}}
            while ($count -ne $finish)
    
        } else {write-host " - No Spn's"}

    }

}


function Get-spAdm-DNS-HostARecord ([string]$Record) {

Write-Debug "Get-spAdm-DNS-HostARecord"
Write-Debug " - Record: $record"

    $return = $null

    $NSLOOKUP = cmd /c "@nslookup -type=CNAME $record 2>nul"

    $NSLOOKUP | % {
    
        if ($_ -match "canonical") {
        
            $Return = (($_).split('=')[1]).replace(" ","")
                        
        }
    
        if ($_ -match "primary name server") {
        
            $return  = $record    
                
        }
    }
  
    if ($return -eq $null) {

        return "Missing"
    }

    else {

        return $return
    }
  
Write-Debug "Get-spAdm-DNS-HostARecord.FINSIHED"
  
}

##############################################################################
#.SYNOPSIS
# Adds support for starting transcript from PowerShell ISE and Shell
#
#.DESCRIPTION
# Starts in-built transcript logging and manually creates transcript file for PowerShell ISE support
#
#.PARAMETER Path
# The path to the transcript file.
#
#.EXAMPLE
# spAdm-Start-Transcript -path 'C:\SPKerberosCheckerTranscript.txt'
##############################################################################
function spAdm-Start-Transcript ([string]$file) {

Write-Debug " FUNCTION spAdm-Start-Transcript"
Write-Debug " - File: $file"

  if ($psISE) {
    $header =
    @"
**************************************
Windows PowerShell ISE Transcript Start
Start Time: $(get-date)
UserName: $env:username
UserDomain: $env:USERDNSDOMAIN
ComputerName: $env:COMPUTERNAME
Windows version: $((Get-WmiObject win32_operatingsystem).version)
**************************************

"@

    Out-File -FilePath $file -InputObject $header
    write-spAdm -Result $? -Message "StartTranscript to '$file' successful" -ErrorMessage "Start-Transcript unable start transcript to '$file', continuing..." -Break $false
  
  }
  else {
    Start-Transcript -Path $file # if not using PowerShell ISE, start transcript normally
    write-spAdm -Result $? -Message "Start-Transcript to '$file' successful" -ErrorMessage "Start-Transcript unable start transcript to '$file', continuing..." -Break $false
    }
  
Write-Debug " FUNCTION spAdm-Start-Transcript.FINSIHED"

}

##############################################################################
#.SYNOPSIS
# Adds support for stopping transcript from PowerShell ISE and Shell
#
#.DESCRIPTION
# Stops in-built transcript logging and for PowerShell ISE support manually creates transcript file
#
#.PARAMETER Path
# The path to the transcript file.
#
#.EXAMPLE
# spAdm-Stop-Transcript -path 'C:\SPKerberosCheckerTranscript.txt'
##############################################################################
function spAdm-Stop-Transcript ([string]$file) {
  
Write-Debug " FUNCTION spAdm-Stop-Transcript"
Write-Debug " - File: $file"
  
  if ($psISE) {
    $out = "Transcript stopped at $(get-date), output file is $($file) "
    $out
    Out-File -FilePath $file -InputObject $out -Append
    # V2 PowerShell
    if($PSVersionTable.PSVersion.Major -eq 2){
    $psise.CurrentPowerShellTab.Output.Text | Out-File -FilePath $file -Append
    write-spAdm -Result $? -Message "StopTranscript to '$file' successful" -ErrorMessage "StopTranscript unable stop transcript in file '$file' , continuing..." -Break $false
    }
    # V3 PowerShell
    if($PSVersionTable.PSVersion.Major -eq 3){
    $psise.CurrentPowerShellTab.ConsolePane.Text | Out-File -FilePath $file -Append
    write-spAdm -Result $? -Message "StopTranscript to '$file' successful" -ErrorMessage "StopTranscript unable stop transcript in file '$file' , continuing..." -Break $false
    }
  }
  else 
    {
    Stop-Transcript
    write-spAdm -Result $? -Message "Stop-Transcript successful" -ErrorMessage "Stop-Transcript unable stop transcript, continuing..." -Break $false
    }
  
Write-Debug " FUNCTION spAdm-Stop-Transcript.FINSIHED"

}